<?php

class Dao_RestrictDAO extends Dao_AbstractDAO {
	
	public function __construct($db = null) {
		parent::__construct($db);
		$this->tableName = "restrict";
		$this->key = "id";
		$this->classModel = "Model_Restrict";
	}
	
	public function getRestrict($page = null, $number = null) {
		$query = $this->createSQLGetRestrict();
		$query = $this->appendLimit($query, $page, $number);
		return $this->getList($query);
	}
	
	private function createSQLGetRestrict() {
		$query = " SELECT * FROM $this->tableName T ";
		$query .= " WHERE T.delete_flg = 0 ";
		return $query;
	}
	
	public function getRestrictByIp($ipAddr) {
		$query = $this->createSQLGetRestrictByIp($ipAddr);
		return $this->getList($query);
	}
	
	private function createSQLGetRestrictByIp($ipAddr) {
		$query = " SELECT * FROM `$this->tableName` ";
		$query .= " WHERE ip_restrict = '$ipAddr' ";
		return $query;
	}
	
	public function delRestrictByExpireDate() {
		$this->startTransaction();
		$query = $this->createSQLDelRestrictByExpireDate();
		$res = $this->db->query($query);
		if ($res === false) {
			$this->rollBackTransaction();
			throw new Exception(mysqli_error($this->db));
		}
		
		$this->commitTransaction();
		
		return $res;
	}
	
	private function createSQLDelRestrictByExpireDate() {
		$query = " DELETE FROM $this->tableName ";
		$query .= " WHERE end_restrict_date < NOW() "; //FROM_UNIXTIME('2013/12/12 12:12:12')
		return $query;
	}
	
	public function getRestrictByEndRestrictDate($restrictId) {
		$query = $this->createSQLGetRestrictByEndRestrictDate($restrictId);
		return $this->getList($query);
	}
	
	private function createSQLGetRestrictByEndRestrictDate($restrictId) {
		$query = " SELECT * FROM `$this->tableName` ";
		$query .= " WHERE id = $restrictId "; 
		$query .= " AND start_restrict_date <= NOW() "; //FROM_UNIXTIME('2013/12/12 12:12:12'
		$query .= " AND end_restrict_date >= NOW() "; //FROM_UNIXTIME('2013/12/12 12:12:12'))

		return $query;
	}
	
	public function getRestrictWithoutDelete($start = null, $number = null) {
		$query = $this->createSQLGetRestrictWithoutDelete($start, $number);
		$query = $this->appendLimit($query, $start, $number);
		return $this->getList($query);
	}
	
	public function countRestrictWithoutDelete() {
		$query = $this->createSQLCountRestrictWithoutDelete();
		$res = $this->query($query);
		if (count($res) > 0) {
			//return $res[0]['CNT'];
			return $res->num_rows;
		} else {
			return 0;
		}
	}
	
	private function createSQLGetRestrictWithoutDelete($start = null, $number = null) {
		$query = " SELECT T.*, cate.name as category_name FROM $this->tableName T ";
		$query .= " LEFT OUTER JOIN category cate ";
		$query .= " ON cate.id = T.id_category ";
		return $query;
	}
	
	private function createSQLCountRestrictWithoutDelete() {
		$query = " SELECT COUNT(*) AS CNT FROM $this->tableName T ";
		return $query;
	}
	
	/**
	 * 
	 * @param string $id
	 * @return Model_Restrict
	 */
	public function getRestrictById($id) {
		return $this->getModelById($id);
	}
	
	public function insertRestrict(Model_Restrict $restrict){
		if ($restrict === null) {
			return false;
		}
	
		$this->startTransaction();
		$success = $this->insertModel($restrict);
	
		if ($success) {
			$this->commitTransaction();
			return true;
		} else {
			$this->rollBackTransaction();
			return false;
		}
	
		return $success;
	}
	
	public function updateRestrict(Model_Restrict $restrict){
		if ($restrict === null) {
			return false;
		}
		$success = false;
	
		$sql = $this->startTransaction();
	
		$success = $this->updateModelById($restrict);
	
		if ($success) {
			$this->commitTransaction();
		} else {
			$this->rollBackTransaction();
		}
	
		return $success;
	}
	
	public function insertOrUpdate(Model_Restrict $restrict) {
		$success = false;
		if ($restrict->getId() !== null) {
				
			$success = $this->updateRestrict($restrict);
		} else {
			$success = $this->insertRestrict($restrict);
		}
		return $success;
	}
	
	// Override
	public function updateModelById(Model_AbstractModel $model) {
		$arr = $model->toArray();
		$sql = $this->createSQLUpdate($arr);
		$res = $this->db->query($sql);
		if ($res === false) {
			throw new Exception(mysqli_error($this->db));
		}

		return $res;
	}

	// Override
	private function createSQLUpdate($arr) {
		$str = '';
		$str .= " UPDATE  `$this->tableName` SET ";
		$index = 0;

		foreach ($arr as $key => $value) {
			$value = $this->removeEscape($value);
			if ($index != 0) {
				$str .= " , ";
			}

			$index++;
			if ($value === null || trim($value) === '') {
				$str .= $key . " = null " ;
			} else if ($key === 'start_restrict_date' || $key === 'end_restrict_date'){
				$str .= $key . " = " .$value . " " ;
			} else {
				$str .= $key . " = '" .$value . "' " ;
			}
		}

		$str .= " , update_time = NOW() , ";
		$str .= " id_user = '$this->idUser' ";

		$str .= " WHERE ";

		$str .= " $this->key =  '" . $arr[$this->key] . "'" ;

		return $str;
	}
	
	// Override
	public function insertModel(Model_AbstractModel $model) {
		$arr = $model->toArray();
		$sql = $this->createSQLInsert($arr);
		$res = $this->db->query($sql);
		if ($res === false) {
			throw new Exception(mysqli_error($this->db));
		}

		return $res;
	}

	// Override
	public function getInsertedId() {
		return $this->db->insert_id;
	}

	// Override
	private function createSQLInsert($arr) {
		$str = '';
		$str .= " INSERT INTO `$this->tableName` (";
		$index = 0;
		foreach ($arr as $key => $value) {
			if ($index != 0) {
				$str .= " , ";
			}

			$index++;
			$str .= $key ;
		}

		$str .= " , insert_time , ";
		$str .= " id_user ";
		$str .= " )";

		$str .= " VALUES (";

		$index = 0;

		foreach ($arr as $key => $value) {
			$value = $this->removeEscape($value);
			if ($index != 0) {
				$str .= " , ";
			}
				$index++;

			if ($value === null || trim($value) === '') {
				$str .= " null " ;
			} else if ($key === 'start_restrict_date' || $key === 'end_restrict_date') {
				$str .= " " . $value . " " ;
			} else {
				$str .= " '" . $value . "' " ;
			}

		}

		$str .= " , now() , ";
		$str .= " '$this->idUser' ";

		$str .= " )";
		echo $str;

		return $str;
	}
		
}